package org.vy.dal;

import java.io.ByteArrayInputStream;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.vy.dal.common.BaseDO;
import org.vy.dal.common.DBTable;
import org.vy.dal.common.DOCache;

public class QueryManager {

	protected static final SimpleDateFormat DTFMT = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

	public static List<? extends BaseDO> executeListQuery(BaseDO req, String sql) throws Exception
	{
		DBConnection con = getConnection(); 
		Statement stmt = con.createStatement();
		String strSQL = formQuery(req, sql);
		ResultSet rst = stmt.executeQuery(strSQL);
		return normalizeList(req, rst);
	}

	public static BaseDO executeQuery(BaseDO req, String sql) throws Exception
	{
		DBConnection con = getConnection(); 
		Statement stmt = con.createStatement();
		String strSQL = formQuery(req, sql);
		ResultSet rst = stmt.executeQuery(strSQL);
		return normalize(req, rst);
	}

	public static BaseDO executePreparedQuery(BaseDO req, String sql, Object[] params) throws Exception
	{
		DBConnection con = getConnection(); 
		String strSQL = formQuery(req, sql);
		PreparedStatement stmt = con.createPreparedStatement(strSQL);
		int i=1;
		for(Object parm : params){
			if(parm instanceof String)
				stmt.setString(i, (String)parm);
			if(parm instanceof Integer)
				stmt.setInt(i, (Integer)parm);
			if(parm instanceof Long)
				stmt.setLong(i, (Long)parm);
			if(parm instanceof Float)
				stmt.setFloat(i, (Float)parm);
			if(parm instanceof Double)
				stmt.setDouble(i, (Double)parm);
			if(parm instanceof Date)
				stmt.setString(i, DTFMT.format((java.util.Date)parm));
			if(parm instanceof ByteArrayInputStream)
				stmt.setBinaryStream(i, (ByteArrayInputStream)parm);
			i++;
		}
		ResultSet rst = stmt.executeQuery();
		return normalize(req, rst);
	}

	public static int executePreparedUpdate(BaseDO req, String sql, Object[] params) throws Exception
	{
		DBConnection con = getConnection();
		String strSQL = formQuery(req, sql);
		PreparedStatement stmt = con.createPreparedStatement(strSQL);
		int i=1;
		for(Object parm : params){
			if(parm instanceof String)
				stmt.setString(i, (String)parm);
			if(parm instanceof Integer)
				stmt.setInt(i, (Integer)parm);
			if(parm instanceof Long)
				stmt.setLong(i, (Long)parm);
			if(parm instanceof Float)
				stmt.setFloat(i, (Float)parm);
			if(parm instanceof Double)
				stmt.setDouble(i, (Double)parm);
			if(parm instanceof Date)
				stmt.setString(i, DTFMT.format((java.util.Date)parm));
			if(parm instanceof ByteArrayInputStream)
				stmt.setBinaryStream(i, (ByteArrayInputStream)parm);
			i++;
		}
		return stmt.executeUpdate();
	}

	public static int executeUpdate(BaseDO req, String strSQL) throws Exception
	{
		if(strSQL.toUpperCase().startsWith("UPDATE")){
			Statement stmt = getConnection().createStatement();
			strSQL = formQuery(req, strSQL);
			System.out.println(strSQL);
			return stmt.executeUpdate(strSQL);
		}else{
			throw new SQLException("Invalid Update query :: "+ strSQL);
		}
	}

	public static BaseDO executeInsert(BaseDO req, String strSQL) throws Exception
	{
		if(strSQL.toUpperCase().startsWith("INSERT")){
			Statement stmt = getConnection().createStatement();
			strSQL = formQuery(req, strSQL);
			System.out.println(strSQL);
			stmt.executeUpdate(strSQL);
			return req;
		}else{
			throw new SQLException("Invalid Insert query :: "+ strSQL);
		}
	}

	public static boolean executeDelete(BaseDO req, String strSQL) throws Exception
	{
		if(strSQL.toUpperCase().startsWith("DELETE")){
			Statement stmt = getConnection().createStatement();
			strSQL = formQuery(req, strSQL);
			return stmt.execute(strSQL);
		}else{
			throw new SQLException("Invalid Delete query :: "+ strSQL);
		}
	}
	public static ResultSet fetch(String sql)throws Exception
	{
		DBConnection con = getConnection(); 
		Statement stmt = con.createStatement();
		//String strSQL = formQuery(req, sql);
		ResultSet rst = stmt.executeQuery(sql);
		return rst;
	}
	static String getSequence(String sql)throws Exception {
		Statement stmt = getConnection().createStatement();
		ResultSet rst = stmt.executeQuery(sql);
		if(rst != null && rst.first()){
			return rst.getString(1);
		}
		return null;
	}

	static int updateSequence(String sql)throws Exception {
		Statement stmt = getConnection().createStatement();
		return stmt.executeUpdate(sql);
	}

//	private static int getSqlType(String sqlType)
//	{
//		int tmp = -1000;
//
//		if(sqlType.equalsIgnoreCase("INT"))
//			tmp = Types.INTEGER;
//		else if(sqlType.equalsIgnoreCase("BIT"))
//			tmp = Types.BIT;
//		else if(sqlType.equalsIgnoreCase("CHAR"))
//			tmp = Types.CHAR;
//		else if(sqlType.equalsIgnoreCase("DATETIME"))
//			tmp = Types.TIMESTAMP;
//		else if(sqlType.equalsIgnoreCase("DECIMAL"))
//			tmp = Types.DECIMAL;
//		else if(sqlType.equalsIgnoreCase("FLOAT"))
//			tmp = Types.FLOAT;
//		else if(sqlType.equalsIgnoreCase("IMAGE"))
//			tmp = Types.BINARY;
//		else if(sqlType.equalsIgnoreCase("MONEY"))
//			tmp = Types.DOUBLE;
//		else if(sqlType.equalsIgnoreCase("NUMERIC"))
//			tmp = Types.NUMERIC;
//		else if(sqlType.equalsIgnoreCase("REAL"))
//			tmp = Types.REAL;
//		else if(sqlType.equalsIgnoreCase("TIMESTAMP"))
//			tmp = Types.TIMESTAMP;
//		else if(sqlType.equalsIgnoreCase("VARCHAR"))
//			tmp = Types.VARCHAR;
//
//		return tmp;
//	}
	
	private static DBConnection getConnection() throws Exception {
		return DALCtx.ctx().getCon();
	}
	
	private static String formQuery(BaseDO req, String sql) throws Exception {
/*		String strSQL = sql, name;
		Object value;
		BeanInfo beanInfo = Introspector.getBeanInfo(req.getClass());
		PropertyDescriptor[] pdList = beanInfo.getPropertyDescriptors();
		for(PropertyDescriptor pd : pdList){
			name = pd.getName();
			if(strSQL.indexOf(name) != -1) {
				value = pd.getReadMethod().invoke(req, (Object[])null);
				strSQL = strSQL.replaceAll(name, getSQLString(value));
			}
		}
*/
		DBTable tbl = req.getClass().getAnnotation(DBTable.class);
		sql = sql.replaceAll("<TABLE>", tbl.value());
		
		Map<String, Method> map = DOCache.getReadMap(req.getClass());
		String strSQL = sql, name;
		Object value = null;
		Method mtd = null;
		Set<Entry<String, Method>> set = map.entrySet();
		for(Entry<String, Method> entry : set){
			name = entry.getKey();
			if(strSQL.indexOf(name) != -1) {
				mtd = entry.getValue();
				value = mtd.invoke(req, (Object[])null);
				strSQL = strSQL.replace(name, getSQLString(value));
			}
		}
		return strSQL;
	}

	private static BaseDO normalize(BaseDO req, ResultSet rst) throws Exception {
		
		if(rst.first()){
			Map<String, Method> map = DOCache.getWriteMap(req.getClass());
			Object[] value = new Object[1];
			Method mtd = null; 
			ResultSetMetaData rstMeta = rst.getMetaData();
			int rstCount = rstMeta.getColumnCount();
			for(int j=1; j < rstCount; j++){
				String name = rstMeta.getColumnName(j).toUpperCase();
				mtd = map.get(name);
				if(mtd != null){
					value[0] = sqlToJava(rst, mtd.getParameterTypes()[0].getName(), name);
					if(value[0] != null)
						mtd.invoke(req, value);
				}
			}
//			Set<Entry<String, Method>> set = map.entrySet();
//			for(Entry<String, Method> entry : set){
//				mtd = entry.getValue();
//				try{
//				value[0] = sqlToJava(rst, mtd.getParameterTypes()[0].getName(), entry.getKey());
////				System.out.println(mtd.getParameterTypes()[0].getName()+"   "+ value[0].getClass().getSimpleName());
//				if(value[0] != null)
//					mtd.invoke(req, value);
//				}catch(Exception e){
//					//DO Nothing, because Select may not have the field but we are trying to retrieve
//				}
//			}
		}
		return req;
	}
	
	private static List<? extends BaseDO> normalizeList(BaseDO req, ResultSet rst) throws Exception {
		
		List<BaseDO> doList = new ArrayList<BaseDO>();
		Map<String, Method> map = DOCache.getWriteMap(req.getClass());
		Class<?> clz = req.getClass();
		ResultSetMetaData rstMtdt = rst.getMetaData();
		
		while(rst.next()){
			Object[] value = new Object[1];
			Method mtd = null;
			BaseDO bdo = (BaseDO)clz.newInstance();
			for(int i=1; i <= rstMtdt.getColumnCount(); i++){
				String key = rstMtdt.getColumnName(i).toUpperCase();
				mtd = map.get(key);
				if(mtd !=null && mtd.getParameterTypes() != null){
					value[0] = sqlToJava(rst, mtd.getParameterTypes()[0].getName(), key);
					if(value[0] != null)
						mtd.invoke(bdo, value);
				}
			}
			doList.add(bdo);
		}
		return doList;
	}

	private static String getSQLString(Object value){
		if(value == null) return "NULL";
		
		if(value instanceof String){
			return "'" + value.toString().replaceAll("'","''") + "'"; 
		}else if(value instanceof Integer
				|| value instanceof Long
				|| value instanceof Float
				|| value instanceof Double
				|| value instanceof BigInteger){
			return value.toString();
		}else if(value instanceof java.util.Date){
			return "'" + DTFMT.format((java.util.Date)value) + "'";
		}
		return value.toString();
	}
	
	private static Object sqlToJava(ResultSet rst, String cls, String name) throws Exception {
		if("java.lang.Long".equals(cls)){
			return rst.getLong(name);
		}
		if("java.lang.Integer".equals(cls)){
			String val = rst.getString(name);
			if(val != null && !"".equals(val))
				return Integer.parseInt(rst.getString(name));
			else
				return null;
		}
		if("java.lang.Float".equals(cls)){
			return rst.getFloat(name);
		}
		if("java.lang.Double".equals(cls)){
			return rst.getDouble(name);
		}
		if("java.lang.Boolean".equals(cls)){
			return rst.getBoolean(name);
		}
		if("java.util.Date".equals(cls)){
			return rst.getDate(name);
		}
		if("java.lang.String".equals(cls)){
			return rst.getString(name);
		}
		if("java.io.ByteArrayInputStream".equals(cls)){
			return (ByteArrayInputStream)rst.getBinaryStream(name);
		}
		return null;
	}
}
